﻿using System;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;

namespace Supermarket.Client
{
    public static class ExcelFilesManager
    {
        public static void ReadExcelData(string rootDirPath)
        {
            DirectoryInfo rootDir = new DirectoryInfo(rootDirPath);
            foreach (var childDir in rootDir.GetDirectories())
            {
                string dirName = childDir.Name;
                DateTime reportDate = DateTime.ParseExact(dirName, "dd-MMM-yyyy", CultureInfo.InvariantCulture);

                foreach (var exFile in childDir.GetFiles())
                {
                    ReadData(exFile, rootDirPath, dirName, reportDate);
                }
            }
        }
  
        private static void ReadData(FileInfo exFile, string rootDirPath, string dirName, DateTime reportDate)
        {
            string fileName = exFile.Name;
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                      @" Data Source=" + rootDirPath + "\\" + dirName + "\\" + fileName + "; Persist Security Info=false; Extended Properties=Excel 8.0;";

            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();

            using (connection)
            {
                string sql = "SELECT * FROM [Sales$]";

                OleDbCommand cmd = new OleDbCommand(sql, connection);
                OleDbDataReader reader = cmd.ExecuteReader();

                int supermarketID = 0;
                int count = 0;
                while (reader.Read())
                {
                    if (count == 0)
                    {
                        supermarketID = MsSqlManager.InsertInSupermarketTable(reader[0].ToString());
                        count++;
                        continue;
                    }
                    else if (count == 1 || reader[0].ToString().Trim() == "…" || reader[0].ToString() == "Total sum:")
                    {
                        count++;
                        continue;
                    }
                    else
                    {
                        int productID = int.Parse(reader[0].ToString());
                        string mysqlProductName = MySqlManager.GetProductNameById(productID);
                        productID = MsSqlManager.GetProductIdByName(mysqlProductName);
                        int quantity = int.Parse(reader[1].ToString());
                        decimal unitPirce = decimal.Parse(reader[2].ToString());
                        decimal sum = decimal.Parse(reader[3].ToString());

                        MsSqlManager.InsertSalesReport(productID, quantity, unitPirce, sum, reportDate, supermarketID);
                    }
                }
            }
        }
    }
}
